package dbAdapter;



import CommonClass.IMG;
import CommonClass.keyword;
import CommonClass.URLlink;
import CommonClass.URLSearch;




import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Queue;
import java.util.ArrayList;
import java.util.Set;
import java.util.Vector;
import java.sql.*;

import javax.swing.text.html.HTMLDocument.Iterator;

public class DatabaseAdapter {
	
	
	private static DatabaseAdapter instance = null;
	
	
	
	private String DATABASE_NAME 			= "imagesearch";
	private String KEYWORD_TABLE	 		= "keyword";
	private String RESULT_TABLE	     		= "result";
	private String SEARCHED_TABLE      		= "searchhistory";
	private String KEYWORD_STATISTICS_TABLE	= "keywordstatistics";
	private String TEMP_SEARCH_TABLE 		= "tempsearchlist";
	private String UNPROCESSED_TABLE		= "unprocessed_searchlist";
	private String DATABASE_USERNAME 		= "root";
	private String DATABASE_PASSWORD 		= "1234";
			
	private	String KEYWORD;
	private	boolean isOccupied				= false;
	
			
	private Connection connect				= null;
	private Statement  stmt 				= null;
	
	public DatabaseAdapter() {
		
		 try {
				connectToDatabase();
				
		} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println(e.getMessage());
		}
		
	}
	

	public static DatabaseAdapter getInstance() throws SQLException{
		
	 if(instance == null) {
         instance = new DatabaseAdapter();
      }
	 
      return instance;
		
	}
	
	public synchronized boolean getMutex(){
			
			if(isOccupied == true){
				return false;
			}
			else{
				isOccupied = true;
				return true;
			}
		
		}
	
	public synchronized boolean returnMutex(){
		
		if(isOccupied == true){
			isOccupied = false;
			return true;
		}
		else{
			return false;
			
		}
	}
	
	public synchronized void clearSearch(){
		
		KEYWORD=null;
		clearUnprocessedURLList();
		clearTempTable();
		return;
	}
	
	public synchronized void setNewSearch(String _keyword, URLlink firstURL){
		
		KEYWORD = _keyword;
		
		clearUnprocessedURLList();
		clearTempTable();
		addToSearchedList(firstURL);
		return;
		
	}

	public void setNewSearch(keyword _keyword, URLlink firstURL){
		
		
		KEYWORD = _keyword.getKeyword();
		clearUnprocessedURLList();
		clearTempTable();
		addToSearchedList(firstURL);
		return ;
	}
	
	public synchronized void addNewUnprocessedUrlList(HashMap<URLlink,Integer> newList){
		
		clearTempTable();
		java.util.Iterator<Entry<URLlink, Integer>> i = newList.entrySet().iterator(); 
		int j = 0;
		String insertQuery = "insert into "+TEMP_SEARCH_TABLE+" values";
		String selectQuery="";
		while(i.hasNext()){
			
			Map.Entry entry = (Map.Entry) i.next(); 
			//insertIntoTempTable((URLlink) entry.getKey(),((Integer) entry.getValue()).intValue());
			URLlink tmplink = (URLlink) entry.getKey();
			int tmpProbability = ((Integer) entry.getValue()).intValue();
			
			if(j == 0){
				
				insertQuery+="('"+tmplink.getURL()+"',"+tmpProbability+")";
			}else{
				
				insertQuery+=",('"+tmplink.getURL()+"',"+tmpProbability+")";
			}
			j++;
				
		}
		executeUpdateQuery(insertQuery);
		j=0;
		insertQuery="";
		
		
		
		String query = "select * from " + TEMP_SEARCH_TABLE  + " where " + TEMP_SEARCH_TABLE + ".URL NOT IN " +
				"(select " + SEARCHED_TABLE+ ".URL from " + SEARCHED_TABLE +") AND " + 
				TEMP_SEARCH_TABLE + ".URL NOT IN (select "+UNPROCESSED_TABLE+".URL from "+ UNPROCESSED_TABLE+")";
				
				
				
	   	   	   
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				String tempURL = result.getString("URL");
				int probability = result.getInt("probability");
				addToUnprocessedTable(tempURL,probability);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			return;
		}
		clearTempTable();	
		
		return;

	
	}
	
	public synchronized void addNewUnprocessedURL(URLlink URL,int probability){
		
		
		String query = "select * from " + SEARCHED_TABLE +","+ UNPROCESSED_TABLE + 
			   	   " where "+ SEARCHED_TABLE + ".URL='" + URL.getURL() + "' OR " + 
			   	    UNPROCESSED_TABLE + ".URL='"+ URL.getURL()+"'";

		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(result == null){
				addToUnprocessedTable(URL.getURL(),probability);
					
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			return;
		}
		clearTempTable();
		
		return;
	}
	
	private void insertIntoTempTable(URLlink URL, int probability){
		
		String query = "insert into " + TEMP_SEARCH_TABLE +" values('"+URL.getURL()+ "',"+probability+")";
		ResultSet result;
		executeUpdateQuery(query);
	}
	
	private void addToSearchedList(URLlink URL){
		
		String query = "select * from "+ SEARCHED_TABLE+ " where " + SEARCHED_TABLE + ".URL='"+URL.getURL()+"'" ;
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			
			if(!result.next() ){
				query = "insert into " + SEARCHED_TABLE +" values('"+URL.getURL()+ "',NOW(),1)";
				
			}
			else{
				query = "update "+SEARCHED_TABLE+ " set " + SEARCHED_TABLE+".searchedCount =" +
						 SEARCHED_TABLE+".searchedCount + 1 where " +SEARCHED_TABLE+".URL='"+URL.getURL()+"'";
			}
			
			executeUpdateQuery(query);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			return;
		}
			
		
		return;
		
	}
	
	private synchronized void addToUnprocessedTable(String URL, int probability){
		
		String query = "insert into " + UNPROCESSED_TABLE +" values('"+URL+"',"+probability+")";
		ResultSet result;
		executeUpdateQuery(query);
		return;
		
	}
	
	private synchronized void addToUnprocessedTable(URLlink URL, int probability){
		
		String query = "insert into " + UNPROCESSED_TABLE +" values('"+URL.getURL()+"',"+probability+")";
		ResultSet result;
		executeUpdateQuery(query);
		return;
		
	}
	
	public synchronized int getNumberOfUnprocessedURL(){
		
		int size = 0;
		
		String query = "select count(*) from " + UNPROCESSED_TABLE;
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(result.next()){
				size = result.getInt(1);
			}
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			return 0;
		}
		
		return size;
	}
	
	public synchronized URLlink getUnprocessedURL(){
		
		String query = "select max("+UNPROCESSED_TABLE+".probability) from " + UNPROCESSED_TABLE;
		String URL = null;
		ResultSet result;
		URLlink tempURL = null;;
		int highest_probability = 0;
		
		try {
			result = executeSelectQuery(query);
			
			if(result != null){
				
				if(result.next()){
					highest_probability = result.getInt(1);
				}
				
				query = "select * from "+UNPROCESSED_TABLE+
						" where "+UNPROCESSED_TABLE+".probability="+highest_probability+" limit 1";
				
				result = executeSelectQuery(query);
				if(result!=null){
					if(result.next()){
						URL = result.getString("URL");
						tempURL = new URLlink(URL);
					}
				}
				else{
					return null;
				}

			}
			else{
				return null;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}	
		query = "delete from "+ UNPROCESSED_TABLE+ " where URL='"+ URL+"'";
		executeUpdateQuery(query);	
		addToSearchedList(tempURL);
		
		
		return tempURL;
		
	}
	
	private void clearUnprocessedURLList(){
		
		String query = "delete from " + UNPROCESSED_TABLE;
		ResultSet result;
		executeUpdateQuery(query);
		
		return;
		
	}
	
	private void clearTempTable(){
		
		ResultSet result;
		String query = "delete from " + TEMP_SEARCH_TABLE;
		executeUpdateQuery(query);
		return;
		
	}
	
	public synchronized void addResult(String URL, IMG image, keyword _keyword){
		
		String query = "insert into " + RESULT_TABLE +
						" values('"+URL+"','"+image.getImageURLlink()+"',0,0)";
		ResultSet result;
		executeUpdateQuery(query);
			
		query = "insert into " + KEYWORD_TABLE +
				" values('"+_keyword.getKeyword()+"','"+image.getImageURLlink()+"','"+URL+"','"+_keyword.getTag()+"')";
		
		executeUpdateQuery(query);
		
	}
	
	public Vector<IMG> getImageFromKeyword(String _keyword) {
		
		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
					   " where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
					    KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE+
					    ".keyword='" +_keyword +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				IMG temp = new IMG(result.getString("imageURL"),
								   result.getInt("height"),
								   result.getInt("width"));
				
				tempImageURLList.add(temp);
			
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println(size);
		return tempImageURLList;
	}

	public Vector<IMG> getImageFromURL(URLlink URL){
		
		int size =0;
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
				   " where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
				    KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ 
				    KEYWORD_TABLE + ".URL='" + URL.getURL() +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				IMG temp = new IMG(result.getString("imageURL"),
								   result.getInt("height"),
								   result.getInt("width"));
				
				tempImageURLList.add(temp);
				
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return tempImageURLList;
		
	}
	
	public Vector<keyword> getKeywordFromURL(URLlink url){
		
		int size =0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where URL='" + url.getURL() +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				keyword temp = new keyword(result.getString("tag"),
										   result.getString("keyword") );
				
				tempKeywordList.add(temp);
			
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println(size);
		
		
		return tempKeywordList;
	}

	public Vector<keyword> getKeywordFromImage(IMG image){
		
		int size = 0;
		Vector<keyword> tempKeywordList = new Vector<keyword>();
		String query = "select * from " + KEYWORD_TABLE + " where imageURL='" + image.getImageURLlink()+"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				keyword temp = new keyword(result.getString("tag"),
										   result.getString("keyword"));
				
				tempKeywordList.add(temp);
			
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		
		return tempKeywordList;
		
	}
	
	public Vector<URLlink> getURLlinkFromImageURL(IMG imageURL){
		
		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + RESULT_TABLE + " where imageURL='" + imageURL.getImageURLlink() +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				URLlink temp = new URLlink(result.getString("URL"));
				
				tempURLlinkList.add(temp);
				
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	
		
		
		return tempURLlinkList;
		
	}
	
	public Vector<URLlink> getURLlinkFromKeyword(String _keyword){
		
		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword='" + _keyword +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				URLlink temp = new URLlink(result.getString("URL"));
				
				tempURLlinkList.add(temp);
				
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
	
		
		return tempURLlinkList;
		
	}
	
	public Vector<IMG> getImageFromSimilarKeyword(String keyword){
		
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
					   " where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
					    KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+ KEYWORD_TABLE +
					    ".keyword LIKE '%" + keyword +"%'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				IMG temp = new IMG(result.getString("imageURL"),
								   result.getInt("height"),
								   result.getInt("width"));
				
				tempImageURLList.add(temp);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
		return tempImageURLList;
		
		
	}
	
	public Vector<URLlink> getURLlinkFromSimilarKeyword(String keyword){
		
		int size =0;
		Vector<URLlink> tempURLlinkList = new Vector<URLlink>();
		String query = "select * from " + KEYWORD_TABLE + " where keyword LIKE '%" + keyword +"%'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				URLlink temp = new URLlink(result.getString("URL"));
				
				tempURLlinkList.add(temp);
			
				size++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
		
		
		
		return tempURLlinkList;
		
		
	}

	public Vector<IMG> getIMGFromMatchingUrlAndKeyword(String _keyword, String urlLink){
		
		Vector<IMG> tempImageURLList = new Vector<IMG>(); 
		String query = "select * from " + KEYWORD_TABLE +","+ RESULT_TABLE + 
					   " where "+ KEYWORD_TABLE + ".URL=" + RESULT_TABLE +".URL AND "+
					    KEYWORD_TABLE + ".imageURL=" + RESULT_TABLE +".imageURL AND "+
					    RESULT_TABLE +".URL='"+urlLink+"' AND "+
					    KEYWORD_TABLE +".keyword LIKE '%" + _keyword +"%'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			while(result.next()){
				
				IMG temp = new IMG(result.getString("imageURL"),
								   result.getInt("height"),
								   result.getInt("width"));
				
				tempImageURLList.add(temp);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		
		return tempImageURLList;
		
	}
	
	public boolean isKeywordExisted(keyword _keyword){
		
		String query = "select * from " + KEYWORD_TABLE + " where keyword='" + _keyword.getKeyword() +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
	}
	
	public boolean isKeywordExisted(String keyword){
		
		String query = "select * from " + KEYWORD_TABLE + " where keyword='" + keyword +"'";
		ResultSet result;
		try {
			result = executeSelectQuery(query);
			if(!result.next())
			{
				return false;
			}
			else
			{
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	private void connectToDatabase() throws SQLException {
		

		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Connected to database");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("Unable to connect to database connection");
		} // Setup the connection with the DB
		
		try {
			connect = DriverManager.getConnection("jdbc:mysql://localhost/"+DATABASE_NAME,
													DATABASE_USERNAME,
													DATABASE_PASSWORD);
			
			
			System.out.println("Connected to database connection");
			
			
		} catch (SQLException e) {
			
			System.out.println("Unable to connect to database");
		}
		
	 }
		
	private ResultSet executeSelectQuery(String query) throws SQLException{
		
		try {
			stmt = connect.createStatement();
			ResultSet result = stmt.executeQuery(query);
			System.out.println("Select Query is successful");

			return result;
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Select Query is unsuccessful");
			System.out.println(e.getMessage());
			return null;
		}
		
	}
	
	private void executeUpdateQuery(String query) {
		
		try{
			
			stmt = connect.createStatement();
			int i = stmt.executeUpdate(query);
			System.out.println("Update Query is successful");
			System.out.println("Row modified: "+ i);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("Update Query is unsuccessful");
			System.out.println(e.getMessage());
			
		}
		
	}

	
	private int getQueryResultSize(ResultSet result) throws SQLException{
		
		int size = 0;
		
		if (result != null)   
		{  
		  result.beforeFirst();  
		  result.last();  
		  size = result.getRow();  
		}  
		return size;
	}
	
}
